/*
 * IBM Corporation.
 * Copyright (c) 2014 All Rights Reserved.
 */

package com.ibm.iisp.dataie.service;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ibm.iisp.common.util.StringUtilsExt;
import com.ibm.iisp.dataie.model.DataIETable;
import com.ibm.iisp.dataie.vo.DataExportRequest;

/**
 * 类作用：
 * @author Johnny@cn.ibm.com
 * 使用说明：
 */
public class DataIEUtils {
	public static final String _FILTER_PARAMS = "FilterParams";
	public static final String _DATA_ROW_START_PARAM = "StartDataRow";
	public static final String _COLUMN_ROW_PARAM = "ColNameRow";

	public static final String LINE_WRAP = "\r\n";
	private static Logger log = LoggerFactory.getLogger(DataIEUtils.class);

	public static Set<String> getRequiredColumns(DataIETable table) {
		String keyCols = table.getKeyColumns();
		String requiredCols = table.getRequiredCoumns();
		Set<String> requiredColNames = new HashSet<>();
		if (keyCols != null) {
			String[] colNames = keyCols.split(",");
			for (String col : colNames) {
				requiredColNames.add(col.trim().toUpperCase());
			}
		}
		if (requiredCols != null) {
			String[] colNames = requiredCols.split(",");
			for (String col : colNames) {
				requiredColNames.add(col.trim().toUpperCase());
			}
		}
		return requiredColNames;
	}

	public static String genDataFileHeader(DataExportRequest dataExportReq) {
		StringBuilder expDataSb = new StringBuilder();
		try {
			CSVPrinter printer = new CSVPrinter(expDataSb, CSVFormat.DEFAULT);
			// 第一行
			Object[] row1 = new Object[3];
			row1[0] = dataExportReq.getTableName();
			row1[1] = StringUtilsExt.MD5(dataExportReq.getTableName());
			row1[2] = "修改本模板请注意：第1行第1-2格是表名签名，不能改;第2行是列名，可以整列删除，但不能修改列名；"
				+ "第3行是列描述，可以修改为可读性较好的名字；第4行起是数据。修改后请保存为xls(推荐Excel2003)或xlsx文件。";
			printer.printRecord(row1);

			// 第二行：列名，第三行：列中文名
			ArrayList<String> row2 = new ArrayList<>(dataExportReq.getColumns().size());
			ArrayList<String> row3 = new ArrayList<>(dataExportReq.getColumns().size());
			for (DataExportRequest.ColumnDef col : dataExportReq.getColumns()) {
				if (Boolean.TRUE.equals(col.getIsExport())) {
					row2.add(col.getName());
					String desc = null;
					if (col.getDesc() == null) {
						desc = col.getName();
					} else {
						int indexOf = col.getDesc().indexOf(";");
						if (indexOf > 0) {
							desc = col.getDesc().substring(0, indexOf);
						} else {
							desc = col.getDesc();
						}
					}
					if (Boolean.TRUE == col.getIsRequired()) {
						desc = "*" + desc;
					}
					row3.add(desc);
				}
			}
			printer.printRecord(row2);
			printer.printRecord(row3);
			printer.close();
		} catch (IOException e) {
			log.error("shouldn't happen.", e);
		}
		return expDataSb.toString();
	}

	/**
	 * @param defaultFilter
	 * @return
	 */
	public static String replaceVariables(String defaultFilter) {
		if (defaultFilter.indexOf("${currentDataDate}") > 0) {
			String dt = (String) RequestContextHolder.getRequestAttributes().getAttribute("currentDataDate",
				RequestAttributes.SCOPE_SESSION);
			defaultFilter = defaultFilter.replace("${currentDataDate}", dt);
		}
		if (defaultFilter.indexOf("${currentNodeCode}") > 0) {
			String dt = (String) RequestContextHolder.getRequestAttributes().getAttribute("currentNodeCode",
				RequestAttributes.SCOPE_SESSION);
			defaultFilter = defaultFilter.replace("${currentNodeCode}", dt);
		}
		return defaultFilter;
	}

	/** 从filterSql对照filterTemplate中变量分析出参数键值对。
	 * 如：filterSql：DATA_DATE = '20161231'
	 *    filterTemplate： DATA_DATE = '${dataDate}'
	 *    分析出的参数：
	 *    {DATA_DATE=20161231，dataDate=20161231}
	 * @param filterSql
	 * @param filterTemplate
	 * @return
	 */
	public static Map<String, ?> parseParams(String filterSql, String filterTemplate) {
		if (filterSql.equals(filterTemplate)) {
			return null;
		}
		// String formula = "DATA_DATE = '20161231' AND BT IS NULL OR NODE_CODE =222000 ";
		String pattern = "(\\w+)\\s*(=|IS)\\s*('?\\S+'?)(\\s*(AND|OR)(\\w+)\\s*(=|IS)\\s*('?\\S+'?))*";
		Pattern p = Pattern.compile(pattern);
		Map<String, String> params = new HashMap<>();
		Matcher m = p.matcher(filterSql);
		while (m.find()) {
			// 如：DATA_DATE = '20161231'
			String key = m.group(1).trim();
			String val = m.group(3).trim();
			// 去掉单引号 e.g. '20161231' -> 20161231
			if (val.startsWith("'") && val.endsWith("'")) {
				val = val.substring(1, val.length() - 1);
			}
			if (!val.startsWith("${")) {
				params.put(key, val);
			}
		}

		m = p.matcher(filterTemplate);
		while (m.find()) {
			// 如DATA_DATE = '${dataDate}'
			String key = m.group(1).trim();
			String val = m.group(3).trim();
			// 去掉单引号 e.g. '${dataDate}' -> ${dataDate}
			if (val.startsWith("'") && val.endsWith("'")) {
				val = val.substring(1, val.length() - 1);
			}
			// 去掉${}, e.g. '${dataDate}' -> dataDate
			if (val.startsWith("${") && val.endsWith("}")) {
				val = val.substring(2, val.length() - 1);
			}
			String nval = params.get(key);
			if (nval != null) {
				params.put(val, nval);// 如：dataDate = '20161231'
			} else {
				params.put(key, val);
			}
		}
		return params;
	}

	/**
	 * @param colValMap
	 * @param sql
	 * @return
	 */
	public static String replaceVariables(Map<String, ?> colValMap, String sql) {
		log.debug(sql);
		for (Map.Entry<String, ?> ent : colValMap.entrySet()) {
			if (sql.indexOf(ent.getKey()) > 0) {
				String placeHolder = "${" + ent.getKey() + "}";
				String val = ent.getValue() == null ? "" : String.valueOf(ent.getValue());
				log.debug("Replace {} to {}", placeHolder, val);
				while (sql.contains(placeHolder)) {
					sql = sql.replace(placeHolder, val);
				}
			}
		}
		log.debug(sql);
		return sql;
	}

	public static Sheet getExportDataSheet(Workbook book) {
		for (int idx = 0; idx < book.getNumberOfSheets(); idx++) {
			Sheet s = book.getSheetAt(idx);
			if (verifySignature(s, null)) {
				return s;
			}
		}
		return null;
	}

	/**
	 * @param sheet
	 *            要验证的sheet
	 * @param tableName
	 *            指定的表名，如不指定则不验证表名是否相同
	 * @return
	 */
	public static boolean verifySignature(Sheet sheet, String tableName) {
		// 得到第一列第一行的单元格
		Row row0 = sheet.getRow(0);
		if (row0 != null && row0.getCell(0) != null && row0.getCell(1) != null) {
			String table = row0.getCell(0).getStringCellValue();
			String signature = row0.getCell(1).getStringCellValue();
			String md5 = StringUtilsExt.MD5(table);
			if ((tableName == null || table.equalsIgnoreCase(tableName)) && md5.equals(signature)) {
				return true;
			}
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	public static Map<String, ?> parseParams(Sheet sht, Map<String, ?> filterParams) {
		Comment cc = sht.getRow(0).getCell(0).getCellComment();
		if (cc != null) {
			String cmt = cc.getString().getString();
			ObjectMapper om = new ObjectMapper();
			om.configure(JsonParser.Feature.ALLOW_UNQUOTED_FIELD_NAMES, true);
			try {
				@SuppressWarnings("rawtypes")
				Map map = om.readValue(cmt, Map.class);
				if (filterParams != null) {
					@SuppressWarnings("rawtypes")
					Map nParams = (Map) map.get(_FILTER_PARAMS);
					if (nParams != null) {
						filterParams.putAll(nParams);
					}
					map.put(_FILTER_PARAMS, filterParams);
				}
				return map;
			} catch (Exception e) {
				log.error("Error to parse json value:" + cmt, e);
			}
		}
		@SuppressWarnings("rawtypes")
		Map params = new HashMap();
		params.put(_FILTER_PARAMS, filterParams);
		return params;
	}

}
